package Datebase;

import java.awt.*;
import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.net.URL;
import java.sql.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class tupdatescore extends JFrame {
    public static void main(String[] args) {
        System.setProperty("sun.java2d.noddraw", "true");
        new  tupdatescore();
    }

    public  tupdatescore(){
        JFrame mod = new JFrame("考试成绩修改界面");
        URL resource = tupdatescore.class.getClassLoader().getResource("kun.png");
        Image image = new ImageIcon(resource).getImage();
        mod.setIconImage(image);
        mod.setLayout(null);
        mod.setSize(800,600);
        mod.setLocationRelativeTo(null);
        mod.setLayout(null);

        JLabel tot1 = new JLabel("请输入学生学号:");
        tot1.setFont(new Font("微软雅黑", Font.PLAIN, 18));
        tot1.setBounds(60,20,400,55);
        mod.add(tot1);

        JTextField textField1 = new JTextField();
        textField1.setFont(new Font("微软雅黑",Font.PLAIN,18));
        textField1.setColumns(20);
        textField1.setBounds(250,35,150,25);
        mod.add(textField1);

        JButton button= new JButton("确认");
        button.setFont(new Font("微软雅黑",Font.PLAIN,18));
        button.setBounds(410,35,75,25);
        mod.add(button);

        JLabel tot2 = new JLabel("该学生目前的成绩:");
        tot2.setFont(new Font("微软雅黑", Font.PLAIN, 20));
        tot2.setBounds(60,75,400,55);


        JLabel tot3 = new JLabel("请输入目标课程号:");
        tot3.setForeground(new Color(0x0A0A0A));
        tot3.setFont(new Font("微软雅黑", Font.PLAIN, 18));
        tot3.setBounds(60, 350, 400, 55);

        JTextField textField2 = new JTextField();
        textField2.setFont(new Font("微软雅黑",Font.PLAIN,18));
        textField2.setColumns(20);
        textField2.setBounds(250,365,150,25);

        JLabel tot4 = new JLabel("请输入修改后成绩:");
        tot4.setForeground(new Color(0x0A0A0A));
        tot4.setFont(new Font("微软雅黑", Font.PLAIN, 18));
        tot4.setBounds(60, 400, 400, 55);

        JTextField textField3 = new JTextField();
        textField3.setFont(new Font("微软雅黑",Font.PLAIN,18));
        textField3.setColumns(20);
        textField3.setBounds(250,415,150,25);

        JButton button1= new JButton("修改");
        button1.setFont(new Font("微软雅黑",Font.PLAIN,18));
        button1.setBounds(255,475,100,50);

        button.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {


                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
                    String user = "HR";
                    String password = "root";
                    Connection conn = DriverManager.getConnection(dbURL, user, password);
                    String sql = "SELECT EXAM.SNO,SNAME,CNAME,EXAM.CNO,GRADE FROM STUDENT,EXAM,COURSE WHERE EXAM.CNO=COURSE.CNO AND EXAM.SNO=STUDENT.SNO AND EXAM.SNO='" + textField1.getText() + "'";

                    PreparedStatement pstm = conn.prepareStatement(sql);
                    ResultSet rs = pstm.executeQuery();

                    int count = 0;
                    while (rs.next()) {
                        count++;
                    }
                    if (count == 0) {
                        JOptionPane.showMessageDialog(null, "学号不存在",
                                "提示", JOptionPane.WARNING_MESSAGE);
                    }
                    else {
                        rs = pstm.executeQuery();

                        Object[][] data = new Object[count][5];
                        String[] title = {"学号", "姓名", "课程名", "课程号", "成绩"};
                        count = 0;
                        while (rs.next()) {
                            data[count][0] = (rs.getString("SNO"));
                            data[count][1] = (rs.getString("SNAME"));
                            data[count][2] = (rs.getString("CNAME"));
                            data[count][3] = (rs.getString("CNO"));
                            data[count][4] = (rs.getString("GRADE"));
                            count++;
                        }
                        JTable table = new JTable(data, title);
                        JScrollPane scrollPane = new JScrollPane(table);
                        DefaultTableCellRenderer cr = new DefaultTableCellRenderer();
                        cr.setHorizontalAlignment(SwingConstants.CENTER);
                        table.setDefaultRenderer(Object.class, cr);

                        table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                        table.getColumnModel().getColumn(0).setPreferredWidth(200);
                        table.getColumnModel().getColumn(1).setPreferredWidth(200);
                        table.getColumnModel().getColumn(2).setPreferredWidth(200);
                        table.getColumnModel().getColumn(3).setPreferredWidth(200);
                        table.getColumnModel().getColumn(4).setPreferredWidth(200);
                        table.setRowHeight(80);
                        table.setFont(new Font("微软雅黑", Font.BOLD, 13));
                        scrollPane.setBounds(90, 140, 600, 185);
                        mod.add(scrollPane);

                        mod.repaint();
                        mod.add(button1);
                        mod.add(tot2);
                        mod.add(tot3);
                        mod.add(textField2);
                        mod.add(tot4);
                        mod.add(textField3);

                    }
                }catch (ClassNotFoundException cnfe) {
                    cnfe.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                }catch (SQLException sqle) {
                    sqle.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
                }

            }
        });
        button1.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
                    String user = "HR";
                    String password = "root";

                    Connection conn = DriverManager.getConnection(dbURL, user, password);
                    String sql = "select * from EXAM where CNO = '" + textField2.getText() + "' AND SNO='"+textField1.getText()+"'";

                    PreparedStatement pstm = conn.prepareStatement(sql);
                    ResultSet rs = pstm.executeQuery();

                    int count = 0;
                    while (rs.next()) {
                        count++;
                    }
                    if(count == 0)
                    {
                        JOptionPane.showMessageDialog(null, "课程号输入有误!",
                                "提示", JOptionPane.WARNING_MESSAGE);
                    }
                    else {
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            String dbURL1 = "jdbc:oracle:thin:@localhost:1521:XE";
                            String user1 = "HR";
                            String password1 = "root";
                            Connection conn1 = DriverManager.getConnection(dbURL1, user1, password1);
                            String sql1 = "update EXAM\n" +
                                    "set GRADE='" + textField3.getText() + "'" +
                                    "WHERE EXAM.CNO='" + textField2.getText() + "' AND EXAM.SNO='" + textField1.getText() + "'";
                            PreparedStatement pstm1 = conn.prepareStatement(sql1);
                            ResultSet rs1 = pstm1.executeQuery();

                            JOptionPane.showMessageDialog(null, "修改成功！", "录入状态", JOptionPane.INFORMATION_MESSAGE);
                        } catch (ClassNotFoundException cnfe) {
                            cnfe.printStackTrace();
                            JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                        } catch (SQLException sqle) {
                            sqle.printStackTrace();
                            JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
                        }
                    }
                } //try1
                catch (ClassNotFoundException cnfe) {
                    cnfe.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                    JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
                }
            }
        });

        mod.setVisible(true);
    }
}